package com.info.zhiduoduo.common.utils.poi;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;

import java.awt.Color;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

public class ExportExcelUtils {

	public static void exportExcel(HttpServletResponse response, String fileName, List<ExcelData> excelDataList) throws Exception {
		// 告诉浏览器用什么软件可以打开此文件
		response.setHeader("content-Type", "application/vnd.ms-excel");
		// 下载文件的默认名称
		response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
		exportExcel(excelDataList, response.getOutputStream());
	}

	public static void exportExcel(List<ExcelData> excelDataList, OutputStream out) throws Exception {

		XSSFWorkbook wb = new XSSFWorkbook();
		try {

			for (ExcelData data : excelDataList) {
				String sheetName = data.getSheetName();
				if (null == sheetName) {
					sheetName = "Sheet1";
				}
				XSSFSheet sheet = wb.createSheet(sheetName);
				writeExcel(wb, sheet, data);
			}

			wb.write(out);
		} finally {
			wb.close();
		}
	}

	private static void writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {

		int rowIndex = 0;
		List<ExcelTitle> titles = data.getExcelTitleList();
		if (null != titles && titles.size() > 0) {
			rowIndex = writeTitlesToExcel(wb, sheet, data.getExcelTitleList());
		}
		rowIndex = writeRowTitlesToExcel(rowIndex, wb, sheet, data.getRowTitles());
		writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);
		//autoSizeColumns(sheet, data.getRowTitles().size() + 1);

	}

	private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<ExcelTitle> titles) {
		int rowIndex = 0;
		//int colIndex = 0;

		Font titleFont = wb.createFont();
		titleFont.setFontName("simsun");
		//titleFont.setBold(true);
		titleFont.setFontHeightInPoints((short) 14);
		titleFont.setColor(IndexedColors.BLACK.index);

		XSSFCellStyle titleStyle = wb.createCellStyle();
		titleStyle.setAlignment(HorizontalAlignment.LEFT);
		titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		//titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
		//titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
		titleStyle.setFont(titleFont);
		setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));

		//这个就是合并单元格
		//参数说明：1：开始行 2：结束行  3：开始列 4：结束列
		//比如我要合并 第二行到第四行的    第六列到第八列     sheet.addMergedRegion(new CellRangeAddress(1,3,5,7));

		for (ExcelTitle excelTitle : titles) {

            /*
            2018.2.10 新增逻辑,当一行需要显示多个title时,使用titleList参数. 即一行有多个合并的单元格,每个单元格显示不同的文字
            如果list中第二个合并的单元格的起始列数小与等于第一个合并的单元格的结束列数,则将起始列重置为第一个合并的单元格的下一列
            */
			List<int[]> mergeParamList = excelTitle.getMergeParamList();
			List<String> titleList = excelTitle.getTitleList();
			if (mergeParamList != null && !mergeParamList.isEmpty() && titleList != null) {
				//一行显示多个 '合并单元格'和'title'
				for (int i = 0; i < mergeParamList.size(); i++) {
					int[] mergeParam = mergeParamList.get(i);
					String title = "";
					if (titleList.size() > i) {
						title = titleList.get(i);
					}

					if (null != mergeParam && mergeParam.length > 0) {
						int bRow = mergeParam[0];
						int eRow = mergeParam[1];
						int bColumn = mergeParam[2];
						int eColumn = mergeParam[3];
						if (bRow != eRow || bColumn != eColumn) {
							//合并单元格
							sheet.addMergedRegion(new CellRangeAddress(bRow, eRow, bColumn, eColumn));
						}
						Row titleRow = sheet.getRow(rowIndex);
						if (titleRow == null) {
							titleRow = sheet.createRow(rowIndex);
						}
						Cell cell = titleRow.createCell(bColumn);
						cell.setCellValue(title);
						cell.setCellStyle(titleStyle);

						//检查后面一个单元格的起始列需要大于这个title的结束列
						if (mergeParamList.size() > i + 1) {
							int endColumns = mergeParam[3];
							int[] nextParam = mergeParamList.get(i + 1);
							if (nextParam[2] <= endColumns) {
								nextParam[2] = endColumns + 1;
							}
							if (nextParam[2] > nextParam[3]) {
								nextParam[3] = nextParam[2];
							}
						}
					}
				}
				rowIndex++;

			} else {
				//一行只显示一个title
				int[] mergeParam = excelTitle.getMergeParam();

				if (null != mergeParam && mergeParam.length > 0) {
					sheet.addMergedRegion(new CellRangeAddress(mergeParam[0], mergeParam[1], mergeParam[2], mergeParam[3]));
					Row titleRow = sheet.createRow(rowIndex);
					Cell cell = titleRow.createCell(0);
					cell.setCellValue(excelTitle.getTitle());
					cell.setCellStyle(titleStyle);

					rowIndex++;
				}
			}

		}

		return rowIndex;
	}

	private static int writeRowTitlesToExcel(int rowIndex, XSSFWorkbook wb, Sheet sheet, List<String> titles) {
		int colIndex = 0;

		Font titleFont = wb.createFont();
		titleFont.setFontName("simsun");
		titleFont.setBold(true);
		// titleFont.setFontHeightInPoints((short) 14);
		titleFont.setColor(IndexedColors.BLACK.index);

		XSSFCellStyle titleStyle = wb.createCellStyle();
		titleStyle.setAlignment(HorizontalAlignment.CENTER);
		titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
		titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		titleStyle.setFont(titleFont);
		setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));

		Row titleRow = sheet.createRow(rowIndex);
		// titleRow.setHeightInPoints(25);
		colIndex = 0;

		for (String field : titles) {
			Cell cell = titleRow.createCell(colIndex);
			cell.setCellValue(field);
			cell.setCellStyle(titleStyle);
			colIndex++;
		}

		rowIndex++;
		return rowIndex;
	}

	private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {
		int colIndex = 0;

		Font dataFont = wb.createFont();
		dataFont.setFontName("simsun");
		// dataFont.setFontHeightInPoints((short) 14);
		dataFont.setColor(IndexedColors.BLACK.index);

		XSSFCellStyle dataStyle = wb.createCellStyle();
		dataStyle.setAlignment(HorizontalAlignment.CENTER);
		dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		dataStyle.setFont(dataFont);
		setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));

		for (List<Object> rowData : rows) {
			Row dataRow = sheet.createRow(rowIndex);
			// dataRow.setHeightInPoints(25);
			colIndex = 0;

			for (Object cellData : rowData) {
				Cell cell = dataRow.createCell(colIndex);
				if (cellData != null) {
					cell.setCellValue(cellData.toString());
				} else {
					cell.setCellValue("");
				}

				cell.setCellStyle(dataStyle);
				colIndex++;
			}
			rowIndex++;
		}
		return rowIndex;
	}

	private static void autoSizeColumns(Sheet sheet, int columnNumber) {

		for (int i = 0; i < columnNumber; i++) {
			int orgWidth = sheet.getColumnWidth(i);
			sheet.autoSizeColumn(i, true);
			int newWidth = (int) (sheet.getColumnWidth(i) + 100);
			if (newWidth > orgWidth) {
				sheet.setColumnWidth(i, newWidth);
			} else {
				sheet.setColumnWidth(i, orgWidth);
			}
		}
	}

	private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
		style.setBorderTop(border);
		style.setBorderLeft(border);
		style.setBorderRight(border);
		style.setBorderBottom(border);
		style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color);
		style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color);
		style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color);
		style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color);
	}
}
